package com.gisuni.sddc.service;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.json.JSONArray;
import org.json.JSONObject;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.json.Json;
import com.gisuni.sddc.dao.ClusterDao;
import com.gisuni.sddc.model.ClusterLandInfo;
import com.gisuni.sddc.model.Response;
import com.gisuni.sddc.platformManager.dao.LayerTableNameDao;
import com.gisuni.sddc.util.ExcelOperation;
import com.gisuni.sddc.util.StringUtil;

@IocBean
public class ClusterService {

	private Log log = Logs.getLog(ClusterService.class);
	@Inject
	private ClusterDao   clusterDao;

	@Inject
	private LayerTableNameDao layerTableNameDao;


	/**
	 * 查询数据-土地拓展管控 未使用
	 * 
	 * @param 
	 * @return
	 */
	public Response clusterData(String clusterParam ,HttpServletRequest request) {
		Response response = new Response();
		response.setSuccess(false);
		String Result=null;
		StringBuffer selectstr=null;
		String userId = "";
		String landType ="";
		StringBuffer projectType = new StringBuffer(" and ( 1=2 ");//项目类型
		boolean projectTypeFlag = false;
		StringBuffer projectProperty = new StringBuffer(" and ( 1=2");//项目属性
		boolean projectPropertyFlag = false;
		StringBuffer landTypeSbf = new StringBuffer(" and ( 1=2");//土地信息拓展方式
		try {
			selectstr = new StringBuffer();
			if(!StringUtil.isNullOrEmpty(clusterParam)){
				String[] paramArr = clusterParam.split("\\|");
				for(int i=0;i<paramArr.length;i++){
					String param = paramArr[i];
					String[] arr = param.split(":");
					if(arr.length>1){
						if(!StringUtil.isNullOrEmpty(arr[1])){
							String value = arr[1];
							switch(arr[0]){
							  case "startTime": selectstr.append(" and REGISTERDATE>=to_date('"+value+"','yyyy-MM-dd')");break; 
							  case "endTime":   selectstr.append(" and REGISTERDATE<=to_date('"+value+"','yyyy-MM-dd')");break; 
							  case "userId":    selectstr.append(" and REGISTERUSER="+value);break; 
							  
							  case "mocha":  
								  if("true".equals(value)){
									  projectType.append(" or PROJECTTYPE='摸查项目'");   projectTypeFlag=true; 
								  }
								  break; 
							  case "qiatan": 
								  if("true".equals(value)){
									  projectType.append( " or PROJECTTYPE='洽谈项目'");  projectTypeFlag=true;  
								  }
								  break; 
							  case "huoqu":  
								  if("true".equals(value)){
									  projectType.append(" or PROJECTTYPE='获取项目");   projectTypeFlag=true;  
								  }
								  break; 
							  
							  case "duanqi": 
								  if("true".equals(value)){
									  projectProperty.append(" or 短期");  projectPropertyFlag=true;  
								  }
								  break; 
							  case "changqi": 
								  if("true".equals(value)){
									  projectProperty.append(" or 中长期");  projectPropertyFlag=true;  
								  }
								  break; 
							  case "landType": landType=value;
							  default: break; 
							}
							
						}
					}
				}
			}
			
			if(!StringUtil.isNullOrEmpty(landType)){
				String[] landArr = landType.split(",");
				for(String s : landArr){
					landTypeSbf.append(" or EXPENDTYPE = '"+s.trim()+"'");
				}
				landTypeSbf.append(")");
				selectstr.append(landTypeSbf.toString());
			}
			
			projectType.append(")");
			projectProperty.append(")");
			if(projectTypeFlag){
				selectstr.append(projectType.toString());
			}
			if(projectPropertyFlag){
				selectstr.append(projectProperty.toString());
			}
			
			
			String testSQL="select t.provice,t.city,t.district,t.projecttype,t.type ,sum(t.totalarea),count(1) totalNum from sde.landexpand t group by t.province,t.city,t.district,t.projecttype,t.type";
			Map resultMap = clusterDao.queryResultList(testSQL);
			
 
			response.setResult("success");
			response.setSuccess(true);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
	
	/**
	 *  聚合土地类型个数/面积
	 *  国有旧厂用地  表中 GYMJ字段不为空
		纳入三旧的旧村 JTMJ字段不为空
		纳入三旧集团旧厂  JTMJ字段不为空
	 * @param 
	 * @return
	 */
	public Response clusterLandData(String clusterParam ,HttpServletRequest request) {
		Response response = new Response();
		response.setSuccess(false);
		String Result=null;
		StringBuffer selectstr=null;
		String userId = "";//TODO
		String landType ="";
		try {
			
			if(!StringUtil.isNullOrEmpty(clusterParam)){
				String g = request.getParameter("clusterParam");
				Map map  = clusterDao.queryLandResult(clusterSql(clusterParam,request));
				List  resultList = markData(map) ; 
				JSONArray result=new JSONArray();
				for(int i = 0 ; i<resultList.size();i++){
					ClusterLandInfo cluster = (ClusterLandInfo) resultList.get(i);
					JSONObject obj = coverModelToJSONObject(cluster);
					result.put(obj);
				}
				Result = result.toString();
			}

			response.setResult(Result);
			response.setSuccess(true);
			return response;
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return response;
		}
	}
	
	/**
	 *  聚合
	 *  外业拓展项目进度类型
	 * @param 
	 * @return
	 */
	public Response clusterProjTypesData(String[] projClusterPara ,HttpServletRequest request) {
		Response response = new Response();
		response.setSuccess(false);
		JSONObject jsonObj = new JSONObject();
		StringBuffer selectstr=null;
		
		try {
			List<Map<String, Object>> mapAct  = clusterDao.queryProjTypesResult(getProjTypesSql(projClusterPara,request));
//			List<Map<String, Object>> mapTarget = clusterDao.queryProjTypesResult(getProjTypesTargetSql(projClusterPara,request));
			List<Map<String, Object>> mapTarget = clusterDao.queryProjTypesResult(getProjTypesTargetSqlByOrg(projClusterPara,request));
			
			
			jsonObj.put("act", Json.toJson(mapAct).toString());
			jsonObj.put("target", Json.toJson(mapTarget).toString());
			response.setResult(jsonObj);
		
			response.setSuccess(true);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
	
	
	//
	public String clusterSql(String clusterParam,HttpServletRequest request) {
		StringBuffer selectstr=null;
		if(!StringUtil.isNullOrEmpty(clusterParam)){
			StringBuffer rolePrivilegeSql = new StringBuffer();//  现在所有人员的都可以查看getPrivWhereSql(request,"fqmc")
			selectstr = new StringBuffer("select null csmc, null fqmc, '0' landType,null num,null area from dual ");
			String[] landArr = clusterParam.split(",");
			
//			for(String s : landArr){
//				if("2".equals(s)){   //国有旧厂用地
// 
//					selectstr.append(" union select t.csmc,t.fqmc,'2' as landType,count(1) num,to_char(sum(t.gymj),'99999999999.99') area from SDDCGISDATA.jcf t where t.syqsx='国有用地' "+rolePrivilegeSql.toString()+"  group by t.csmc,t.fqmc");
// 
//				}else if("7".equals(s)){ //纳入三旧的旧村
//					selectstr.append(" union select t.csmc,t.fqmc,'7' as landType,count(1) num,to_char(sum(t.jtmj),'99999999999.99') area from SDDCGISDATA.jcu t where t.jtmj>0 "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
//				}else if("9".equals(s)){ //纳入三旧集体旧厂
// 
//					selectstr.append(" union select t.csmc,t.fqmc,'9' as landType,count(1) num,to_char(sum(t.jtmj),'99999999999.99') area from SDDCGISDATA.jcf t where t.syqsx='集体用地' "+rolePrivilegeSql.toString()+"   group by t.csmc,t.fqmc");
// 
//				}else if("10".equals(s)){ //村自留地   留用地
//					selectstr.append(" union select t.csmc,t.fqmc,'10' as landType ,count(1) num,to_char(sum(t.mj),'99999999999.99') area from SDDCGISDATA.lyd t where 1=1 "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
//				}else if("3".equals(s)){ //纳入三旧的旧城
//					selectstr.append(" union select t.csmc,t.fqmc,'3' as landType,count(1) num,to_char(sum(t.gymj),'99999999999.99') area from SDDCGISDATA.jch t where t.SYQSX='国有用地' "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
//				}else if("4".equals(s)){ //国有储备用地
//					selectstr.append(" union select t.csmc,t.fqmc,'4' as landType ,count(1) num,to_char(sum(t.ydmj),'99999999999.99') area from SDDCGISDATA.jhgd t where 1=1 "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
//				}
//			}
			Map<String, String> map  = layerTableNameDao.getTableName();
			
					
			for(String s : landArr){
				if("2".equals(s)){   //国有旧厂用地
 
					selectstr.append(" union select t.csmc,t.fqmc,'2' as landType,count(1) num,to_char(sum(t.gymj),'99999999999.99') area from "+map.get("SJJC")+" t where t.syqsx='国有用地' "+rolePrivilegeSql.toString()+"  group by t.csmc,t.fqmc");
 
				}else if("7".equals(s)){ //纳入三旧的旧村
					selectstr.append(" union select t.csmc,t.fqmc,'7' as landType,count(1) num,to_char(sum(t.jtmj),'99999999999.99') area from "+map.get("NRSJDJC")+" t where t.jtmj>0 "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
				}else if("9".equals(s)){ //纳入三旧集体旧厂
 
					selectstr.append(" union select t.csmc,t.fqmc,'9' as landType,count(1) num,to_char(sum(t.jtmj),'99999999999.99') area from "+map.get("SJJC")+" t where t.syqsx='集体用地' "+rolePrivilegeSql.toString()+"   group by t.csmc,t.fqmc");
 
				}else if("10".equals(s)){ //村自留地   留用地
					selectstr.append(" union select t.csmc,t.fqmc,'10' as landType ,count(1) num,to_char(sum(t.mj),'99999999999.99') area from "+map.get("CZLD")+" t where 1=1 "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
				}else if("3".equals(s)){ //纳入三旧的旧城
					selectstr.append(" union select t.csmc,t.fqmc,'3' as landType,count(1) num,to_char(sum(t.gymj),'99999999999.99') area from "+map.get("SJJCH")+" t where t.SYQSX='国有用地' "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
				}else if("4".equals(s)){ //国有储备用地
					selectstr.append(" union select t.csmc,t.fqmc,'4' as landType ,count(1) num,to_char(sum(t.ydmj),'99999999999.99') area from "+map.get("GYCBYD")+" t where 1=1 "+rolePrivilegeSql.toString()+" group by t.csmc,t.fqmc");
				}
			}
			
			
		}
		return 	selectstr.toString();
	}
	
	/**
	 * 获取每个集团的实际指标数
	 * 
	 * @param projTypes
	 * @param request
	 * @return
	 */
	public String getProjTypesSql(String[] projTypes,HttpServletRequest request){
		String strPrivWhereSql = getPrivWhereSql(request,"qj");
		String strTableName = projTypes[0];
		String dateWhere = "";
		String startDate = projTypes[1];
		String endDate = projTypes[2];
		if(!startDate.equals("")&&endDate.equals("")){
			dateWhere = " and djrq>=to_date('"+startDate+"','yyyy-mm-dd') ";
		}else if(startDate.equals("")&&!endDate.equals("")){
			dateWhere = " and djrq<=to_date('"+endDate+"','yyyy-mm-dd') ";
		}else if(!startDate.equals("")&&!endDate.equals("")){
			dateWhere = " and (t.djrq between to_date('"+startDate+"','yyyy-mm-dd') and to_date('"+endDate+"','yyyy-mm-dd')) ";
		} 
//		已cs,qj字段来计算指标
//		String strProjTypesSql = "select regtype, reg,wm_concat(to_char(xmjd||'&'||num)) xmjdtypes from (select 'qj' regtype, qj reg,xmjd,count(xmjd) num from "+strTableName+"  where 1=1 " + strPrivWhereSql + dateWhere + " group by xmjd,qj  union " +
//				"select 'cs' regtype, cs reg,xmjd,count(xmjd) num from "+strTableName+" where 1=1 " + strPrivWhereSql + dateWhere + " group by xmjd,cs ) group by reg ,regtype";
		

		
		
		//以人-组织来计算指标: 根据外业拓展中的人关联组织机构中所在的集团/公司部门计算[只有集团负责人可查看对应的集团数据]
//		StringBuffer sbf = new StringBuffer();
//		sbf.append(" select 'cs' regtype,t4.reg,wm_concat(to_char(sumXmjd)) xmjdtypes from ( select t3.reg,t3.xmjd||'&'||count(1) sumXmjd  from ( ");
//		sbf.append(" select t.xmjd, t.username,t1.reg from sddcgisdata.wytz t left join (select user_name,");
//		sbf.append(" case when c. organization_name='白云组' then '广州市'   when c. organization_name='花都组' then '广州市'");
//		sbf.append(" when c. organization_name='广州南' then '广州市' when c. organization_name='广州北' then '广州市'");
//		sbf.append(" else substr(c.organization_name,0,length(c.organization_name)-2)||'市' end reg");
//		sbf.append(" from sddcgisplatform.user_info a,sddcgisplatform.user_organization b ");
//		sbf.append("  ,sddcgisplatform.organization c  where a.user_id = b.user_id and c.organization_type='1' and c.id=b.organization_company_id) t1 ");
//		sbf.append("  on t.username = t1.user_name where t.username is not null "+dateWhere);  
//		sbf.append(" ) t3 group by t3.reg,t3.xmjd");
//		sbf.append(" ) t4 group by t4.reg");
		
		
		Map<String, String> map  = layerTableNameDao.getTableName();
		
		//以人-组织来计算指标: 根据外业拓展中的人关联组织机构中所在的集团/公司部门计算 [所有人都可查看]
		StringBuffer sbf = new StringBuffer();
		sbf.append(" select 'cs' regtype,t4.reg,wm_concat(to_char(sumXmjd)) xmjdtypes from ( select t3.reg,t3.xmjd||'&'||count(1) sumXmjd  from ( ");
		sbf.append(" select t.xmjd, t.username,t1.reg from "+map.get("WYTZ")+" t left join (select user_name,");
		sbf.append(" case when c. organization_name='白云组' then '广州市'   when c. organization_name='花都组' then '广州市'");
		sbf.append(" when c. organization_name='广州南' then '广州市' when c. organization_name='广州北' then '广州市'");
		sbf.append(" else substr(c.organization_name,0,length(c.organization_name)-2)||'市' end reg");
		sbf.append(" from sddcgisplatform.user_info a,sddcgisplatform.user_organization b ");
		sbf.append("  ,sddcgisplatform.organization c  where a.user_id = b.user_id  and c.id=b.organization_company_id) t1 "); //缩略了and c.organization_type='1'
		sbf.append("  on t.username = t1.user_name where t.username is not null "+dateWhere);  
		sbf.append(" ) t3 group by t3.reg,t3.xmjd");
		sbf.append(" ) t4 group by t4.reg");
		
		return sbf.toString();
	}
	
	
	/**
	 * 获取每个集团的指标数[人 - 月 ] [负责人不能计入指标数中]
	 * 通过用户组织表中是拓展组的人,再根据集团ID分组,关联组织表得到对应集团组织名称[拓展组ID不为空,负责人不计入指标数]
	 * [注:由于组织与区域没有关联关系,所以指标通过集团名称来分辨 ]
	 * @param projTypes
	 * @param request
	 * @return
	 */
	private String getProjTypesTargetSql(String[] projTypes,HttpServletRequest request) {
		String startDate = projTypes[1];
		String endDate = projTypes[2];
		String strSql = "select substr(orgName,0,length(orgName)-2)||'市' city,"
						+  " round(sum(usenum)*MONTHS_BETWEEN(TO_DATE('" + endDate + "', 'YYYY-MM-DD'), TO_DATE('" + startDate + "', 'YYYY-MM-DD'))) empMonthNum from (select  case orgTable.Organization_Name " 
							+ " when '广州南' then '广州公司' "
							+ " when '白云组' then '广州公司' "
							+ " when '广州北' then '广州公司' "
							+ " when '花都组' then '广州公司' "
		          + " else orgTable.Organization_Name end orgName,userStaticTable.useNum from "
		          + " (select organization_company_id companyId,count(organization_company_id) useNum from sddcgisplatform.user_organization where organization_develop_id is not null and organization_company_id in ( "
		          + " select id from sddcgisplatform.organization where  organization_type=1  "
		          + " ) and user_id not in ((select ORGANIZATION_MANAGER_ID from sddcgisplatform.organization where ORGANIZATION_MANAGER_ID is not null)) group by organization_company_id) userStaticTable "
		          + " left join sddcgisplatform.organization  orgTable " 
		          + " on userStaticTable.companyId = orgTable.Id) group by orgName ";
		return strSql;
	}
	
	/**
	 * 获取每个集团的指标数 
	 * 通过组织配额计算 与时间无关了
	 *  
	 * @param projTypes
	 * @param request
	 * @return
	 */
	private String getProjTypesTargetSqlByOrg(String[] projTypes,HttpServletRequest request) {
		String startDate = projTypes[1];
		String endDate = projTypes[2];
		StringBuffer sbf = new StringBuffer();
//		sbf.append(" select substr(orgNames, 0, length(orgNames) - 2) || '市' city,wytz_type, ");
//		sbf.append(" target empMonthNum  ");                           
//		sbf.append("from (select orgNames,wytz_type,sum(target) target from (select   ");
//		sbf.append(" case orgName when '广州南' then '广州公司' ");
//		sbf.append("   when '白云组' then '广州公司' ");
//		sbf.append("  when '广州北' then '广州公司' ");
//		sbf.append("  when '花都组' then '广州公司' else orgName end orgNames,wytz_type,target    ");              
//		sbf.append("from (select Organization_Name orgName,wytz_type,target from sddcgisplatform.v_org_company_target  ");
//		sbf.append("  left join sddcgisplatform.organization  on org_id =  id)) group by orgNames,wytz_type )");
		
       /**
        * 
        *  select orgNames,  wm_concat(target) empMonthNum
   from (select substr(orgNames, 0, length(orgNames) - 2) || '市' orgNames, wytz_type||'-'||sum(target) target
           from (select case orgName when '广州南' then '广州公司'
            when '白云组' then '广州公司' when '广州北' then '广州公司' when '花都组' then  '广州公司'  else orgName end orgNames,  wytz_type, target
              from (select Organization_Name orgName, wytz_type, target from sddcgisplatform.v_org_company_target
            left join sddcgisplatform.organization on org_id = id)) group by orgNames, wytz_type) group by orgNames
            
        */
		
		sbf.append("select orgNames city,  wm_concat(target) empMonthNum ");
		sbf.append(" from (select substr(orgNames, 0, length(orgNames) - 2) || '市' orgNames, wytz_type||'-'||sum(target) target ");
		sbf.append("from (select case orgName when '广州南' then '广州公司' ");
		sbf.append("when '白云组' then '广州公司' when '广州北' then '广州公司' when '花都组' then  '广州公司'  else orgName end orgNames,  wytz_type, target ");
		sbf.append(" from (select Organization_Name orgName, wytz_type, target from sddcgisplatform.v_org_company_target ");
		sbf.append(" left join sddcgisplatform.organization on org_id = id)) group by orgNames, wytz_type) group by orgNames ");
		return sbf.toString();
	}
	
	/**
	 * 角色可视区域SQL 
	 * @param request
	 * @param column 字段
	 * @return
	 */
	public String getPrivWhereSql(HttpServletRequest request,String column){
		 
			StringBuffer rolePrivilegeSql = new StringBuffer();
			String roles  = "";
			Boolean isManager = false;
//			String result = "[{\"DISPLAYNAME\":\"管理员\",\"ROLENAME\":\"管理员\",\"USERNAME\":\"gh\",\"USERID\":\"74\"}]";//不用数据库版版
			String userInfo = (String) request.getSession().getAttribute("userInfo");
			String roleArea = (String) request.getSession().getAttribute("roleArea");
			
			//判断是否为管理员类型
			JSONArray resultJsonArr = new JSONArray(userInfo);
			if (resultJsonArr != null && resultJsonArr.length() > 0) {
				for (int i = 0; i < resultJsonArr.length(); i++) {
					JSONObject ret = (JSONObject) resultJsonArr.get(i);
					Iterator iterator = ret.keys();
					while (iterator.hasNext()) {
						String key = (String) iterator.next();
						String value = (String) ret.get(key);
						if("USERTYPE".equals(key)&&"0".equals(value)){
							isManager = true;
							break;
						}
					}
					 
				}
			}
			
			//拼接角色可视区域SQL
			if(!"".equals(roleArea.trim())){
				String[] strs = roleArea.split(",");
				rolePrivilegeSql.append("and "+column+" in ('0'");
				for(String roleName : strs){
	       			rolePrivilegeSql.append(",'"+roleName+"'");
				}
				rolePrivilegeSql.append(")");
			}else{
				rolePrivilegeSql.append(" and  1=2 ");
			}
			
			//管理员可查全部
			if(isManager){
				rolePrivilegeSql = new StringBuffer();
			}
			return 	rolePrivilegeSql.toString();
	}
	
    /** 
     * 将Model转换成JSONObject 
     */  
    @SuppressWarnings("unchecked")  
    public   JSONObject coverModelToJSONObject(Object o) throws Exception{  
        JSONObject json = new JSONObject();  
        Class clazz = o.getClass();  
        Field [] fields = clazz.getDeclaredFields();  
        for(int i=0; i< fields.length; i++){  
            Field f = fields[i];  
            json.put(f.getName(), invokeMethod(clazz,f.getName(),o));  
        }   
        return json;  
    }  
    
    private   Object invokeMethod(Class c, String fieldName,Object o){  
        String methodName = fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);  
        Method method = null;  
        try {  
            method = c.getMethod("get" + methodName);  
            return method.invoke(o);  
        }catch (Exception e) {  
        	log.error(e.getMessage());
            return "";  
        }   
           
    }  
    
	
	//对数据就行分类处理
	public List markData(Map map){
		Set citySet = (Set) map.get("citySet");
		Set distinctSet = (Set) map.get("distinctSet");
		List<ClusterLandInfo>  list = (List<ClusterLandInfo>) map.get("totalData");
		List<ClusterLandInfo>  resultList = new ArrayList<ClusterLandInfo>();
 		if(citySet != null){
 			List<ClusterLandInfo>  distinctList = new ArrayList<ClusterLandInfo>();
 			List<ClusterLandInfo>  cityList = new ArrayList<ClusterLandInfo>();
 				//行政区级别数据
 	 			Iterator iteratorDistinct  = distinctSet.iterator();
 	 			while(iteratorDistinct.hasNext()){
 	 				ClusterLandInfo temp = new ClusterLandInfo();
 	 				String distName= (String) iteratorDistinct.next();
 					 if("广州市天河区".equals(distName)){
//	 						 System.out.println();
	 					 }
 	 				for(ClusterLandInfo clusterInfo : list){
 	 	 				 if(distName.equals(clusterInfo.getCity()+clusterInfo.getDistrict())){
 	 	 					temp.setCity(clusterInfo.getCity());
 	 	 					temp.setDistrict(clusterInfo.getDistrict());
 	 	 					if(temp.getGyjcydCount()==null)
 	 	 					temp.setGyjcydCount(clusterInfo.getGyjcydCount());
 	 	 					if(temp.getSjjcCount()==null)
 	 	 					temp.setSjjcCount(clusterInfo.getSjjcCount());
 	 	 					if(temp.getSjjtjcCount()==null)
 	 	 					temp.setSjjtjcCount(clusterInfo.getSjjtjcCount());
 	 	 					if(temp.getCzldCount()==null)
 	 	 					temp.setCzldCount(clusterInfo.getCzldCount());
 	 	 					if(temp.getSjjchCount()==null)//三旧旧城
 	 	 					temp.setSjjchCount(clusterInfo.getSjjchCount());
 	 	 					if(temp.getGysbydCount()==null)//国有储备
 	 	 					temp.setGysbydCount(clusterInfo.getGysbydCount());
 	 	 					
 	 	 					//面积
 	 	 					if(temp.getGyjcydArea()==null)
 	 	 	 				temp.setGyjcydArea(clusterInfo.getGyjcydArea());
 	 	 	 				if(temp.getSjjcArea()==null)
 	 	 	 				temp.setSjjcArea(clusterInfo.getSjjcArea());
 	 	 	 				if(temp.getSjjtjcArea()==null)
 	 	 	 				temp.setSjjtjcArea(clusterInfo.getSjjtjcArea());
 	 	 	 				if(temp.getCzldArea()==null)
 	 	 	 				temp.setCzldArea(clusterInfo.getCzldArea());
 	 	 	 				if(temp.getSjjchArea()==null)
 	 	 	 				temp.setSjjchArea(clusterInfo.getSjjchArea());
 	 	 	 				if(temp.getGysbydArea()==null)
 	 	 	 				temp.setGysbydArea(clusterInfo.getGysbydArea());
 	 	 	 				
 	 	 				 }
 	 				}
 	 				distinctList.add(temp);
 	 			}
 	 			
 	 			//城市级别数据
 	 			Iterator cityDistinct  = citySet.iterator();
 	 			while(cityDistinct.hasNext()){
 	 				ClusterLandInfo temp = new ClusterLandInfo();
 	 				String cityName= (String) cityDistinct.next();
 	 				int gyjcydCount = 0;
 	 				int sjjcCount = 0;
 	 				int sjjtjcCount = 0;
 	 				int czldCount = 0;
 	 				int sjjchCount = 0;
 	 				int gysbydCount = 0;
 	 				//
 	 				double gyjcydArea = 0;
 	 				double sjjcArea = 0;
 	 				double sjjtjcArea = 0;
 	 				double czldArea = 0;
 	 				double sjjchArea = 0;
 	 				double gysbydArea = 0;
 	 				
 	 				for(ClusterLandInfo clusterInfo : list){
 	 	 				 if(cityName!=null && cityName.equals(clusterInfo.getCity())){
 	 	 					temp.setCity((clusterInfo.getCity()));//只对city字段赋值
 	 	 					//个数
 	 	 					if(clusterInfo.getGyjcydCount()!=null)
 	 	 					gyjcydCount+=(Integer.parseInt(clusterInfo.getGyjcydCount()));
 	 	 					
 	 	 					if(clusterInfo.getSjjcCount()!=null)
 	 	 					sjjcCount+=(Integer.parseInt(clusterInfo.getSjjcCount()));
 	 	 					
 	 	 					if(clusterInfo.getSjjtjcCount()!=null)
 	 	 					sjjtjcCount+=(Integer.parseInt(clusterInfo.getSjjtjcCount()));
 	 	 					
 	 	 					if(clusterInfo.getCzldCount()!=null)
 	 	 					czldCount+=(Integer.parseInt(clusterInfo.getCzldCount()));
 	 	 					
 	 	 					
 	 	 					if(clusterInfo.getSjjchCount()!=null)//三旧旧城
 	 	 						sjjchCount+=(Integer.parseInt(clusterInfo.getSjjchCount()));
 	 	 					
 	 	 					if(clusterInfo.getGysbydCount()!=null)//国有储备
 	 	 						gysbydCount+=(Integer.parseInt(clusterInfo.getGysbydCount()));
 	 	 					
 	 	 					
 	 	 					//面积
 	 	 					if(clusterInfo.getGyjcydArea()!=null)
 	 	 					gyjcydArea+=(Double.parseDouble(clusterInfo.getGyjcydArea())/10000);
 	 	 	 					
 	 	 	 				if(clusterInfo.getSjjcArea()!=null)
 	 	 	 				sjjcArea+=(Double.parseDouble(clusterInfo.getSjjcArea())/10000);
 	 	 	 					
 	 	 	 				if(clusterInfo.getSjjtjcArea()!=null)
 	 	 	 				sjjtjcArea+=(Double.parseDouble(clusterInfo.getSjjtjcArea())/10000);
 	 	 	 					
 	 	 	 				if(clusterInfo.getCzldArea()!=null)
 	 	 	 				czldArea+=(Double.parseDouble(clusterInfo.getCzldArea())/10000);
 	 	 	 				
 	 	 	 				if(clusterInfo.getSjjchArea()!=null)
 	 	 	 				sjjchArea+=(Double.parseDouble(clusterInfo.getSjjchArea())/10000);
 	 	 	 				
 	 	 	 				if(clusterInfo.getGysbydArea()!=null)
 	 	 	 				gysbydArea+=(Double.parseDouble(clusterInfo.getGysbydArea())/10000);
 	 	 	 				
 	 	 	 				
 	 	 					
 	 	 				 }
 	 				}
 	 				
 	 				    java.text.DecimalFormat   df   =new   java.text.DecimalFormat("#.00"); 
 	 				
 	 					//个数
	 					temp.setGyjcydCount(gyjcydCount+"");
	 					temp.setSjjcCount(sjjcCount+"");
	 					temp.setSjjtjcCount(sjjtjcCount+"");
	 					temp.setCzldCount(czldCount+"");
	 					temp.setSjjchCount(sjjchCount+"");
	 					temp.setGysbydCount(gysbydCount+"");
	 					//面积
	 					temp.setGyjcydArea(df.format(gyjcydArea)+"");
	 					temp.setSjjcArea(df.format(sjjcArea)+"");
	 					temp.setSjjtjcArea(df.format(sjjtjcArea)+"");
	 					temp.setCzldArea(df.format(czldArea)+"");
	 					temp.setSjjchArea(df.format(sjjchArea)+"");
	 					temp.setGysbydArea(df.format(gysbydArea)+"");
 	 				cityList.add(temp);
 	 			}
 	 			resultList.addAll(cityList);
 	 			resultList.addAll(distinctList);
 		}
		
		return resultList;
		
	}
	


	/**
	 * 聚合面积/个数 详细信息
	 * @param clusterParam
	 * @param request
	 * @return
	 */
	public Response clusterDetailData(String clusterParam, HttpServletRequest request) {
		 
		Response response = new Response();
		response.setSuccess(false);
		try{
		 StringBuffer sbf = null;
		// 区域时
		String areaName = request.getParameter("areaName");
		//类型
		String landType = request.getParameter("landType");
		
		 if(!StringUtil.isNullOrEmpty(areaName)){
			  
			 sbf  =  new StringBuffer(); 
			 StringBuffer whereSql = new StringBuffer();
			//维度
			String dimension = request.getParameter("dimension");
			if("distinct".equals(dimension)){
				whereSql.append(" and fqmc = '"+areaName+"'");
			}else if("city".equals(dimension)){
				whereSql.append(" and csmc = '"+areaName+"'");
			}
			Map<String, String> map  = layerTableNameDao.getTableName();
			
//			if("2".equals(landType)){   //国有旧厂用地
//				sbf = new StringBuffer("   select * from sddcgisdata.jcf t where t.syqsx='国有用地' "+whereSql.toString() );
//			}else if("7".equals(landType)){ //纳入三旧的旧村
//				sbf = new StringBuffer("   select * from sddcgisdata.jcu t where t.jtmj>0 "+whereSql.toString() );
//			}else if("9".equals(landType)){ //纳入三旧集体旧厂
//				sbf = new StringBuffer("   select * from sddcgisdata.jcf t where t.syqsx='集体用地' "+whereSql.toString() );
//			}else if("10".equals(landType)){ //村自留地   留用地
//				sbf = new StringBuffer("   select * from sddcgisdata.lyd t where 1=1 "+whereSql.toString() );
//			}else if("3".equals(landType)){ //纳入三旧的旧城
//				sbf = new StringBuffer(" select * from sddcgisdata.jch t where t.SYQSX='国有用地' "+whereSql.toString() );
//			}else if("4".equals(landType)){ //国有储备用地
//				sbf = new StringBuffer(" select * from sddcgisdata.jhgd t where 1=1 "+whereSql.toString() );
//			}
			
			if("2".equals(landType)){   //国有旧厂用地
				sbf = new StringBuffer("   select * from "+map.get("SJJC")+" t where t.syqsx='国有用地' "+whereSql.toString() );
			}else if("7".equals(landType)){ //纳入三旧的旧村
				sbf = new StringBuffer("   select * from "+map.get("NRSJDJC")+" t where t.jtmj>0 "+whereSql.toString() );
			}else if("9".equals(landType)){ //纳入三旧集体旧厂
				sbf = new StringBuffer("   select * from "+map.get("SJJC")+" t where t.syqsx='集体用地' "+whereSql.toString() );
			}else if("10".equals(landType)){ //村自留地   留用地
				sbf = new StringBuffer("   select * from "+map.get("CZLD")+" t where 1=1 "+whereSql.toString() );
			}else if("3".equals(landType)){ //纳入三旧的旧城
				sbf = new StringBuffer(" select * from "+map.get("SJJCH")+" t where t.SYQSX='国有用地' "+whereSql.toString() );
			}else if("4".equals(landType)){ //国有储备用地
				sbf = new StringBuffer(" select * from "+map.get("GYCBYD")+" t where 1=1 "+whereSql.toString() );
			}
			
			
			String result =  clusterDao.queryNormalResultList(sbf.toString());
			response.setResult(result);
			response.setSuccess(true);
		 }
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
		
		return response;
	}
 
	/**
	 * 聚合项目状态 详细信息
	 * @param clusterParam
	 * @param request
	 * @return
	 */
	public Response clusterProjTypeDetailData(String clusterParam, HttpServletRequest request) {
		 
		Response response = new Response();
		response.setSuccess(false);
		try{
		 StringBuffer sbf = null;
		// 区域时
		String areaName = request.getParameter("areaName");
		//项目类型
		String xmjd = request.getParameter("xmjd");
		//查询的数据库表名
		String tableName = request.getParameter("tableName");
		//开始时间
		String starteDate = request.getParameter("starteDate");
		//结束时间
		String endDate = request.getParameter("endDate");
		
		 if(!StringUtil.isNullOrEmpty(areaName)){
			  
			 sbf  =  new StringBuffer(); 
			 StringBuffer whereSql = new StringBuffer();
//			 以城市查询
//			String dimension = request.getParameter("dimension");
//			if("distinct".equals(dimension)){
//				whereSql.append(" and qj = '"+areaName+"'");
//			}else if("city".equals(dimension)){
//				whereSql.append(" and cs = '"+areaName+"'");
//			}
//			whereSql.append(" and xmjd = '"+xmjd+"' ");
//			sbf = new StringBuffer("   select * from "+tableName+" t where 1=1 "+whereSql.toString() );
			
			 //
			
			 String companyName = null;
			 switch(areaName){
				 case  "东莞市": companyName = "'东莞公司'"; break;
				 case  "清远市": companyName = "'清远公司'"; break;
				 case  "惠州市": companyName = "'惠州公司'"; break;
				 case  "中山市": companyName = "'中山公司'"; break;
				 case  "佛山市": companyName = "'佛山公司'"; break;
				 case  "深圳市": companyName = "'深圳公司'"; break;
				 case  "珠海市": companyName = "'珠海公司'"; break;
				 case  "长沙市": companyName = "'长沙公司'"; break;
			 }
			 
			 String orgWhereSql = " where organization_name = "+companyName;
			 if("广州市".equals(areaName))
				 orgWhereSql = "where organization_name = '广州南'  or organization_name = '白云组'  or organization_name = '广州北' or organization_name = '花都组'";
			 
			 sbf.append("select * from "+tableName+" t where t.xmjd='"+xmjd+"'");
                sbf.append(" and   (djrq between to_date('"+starteDate+"', 'yyyy-mm-dd') and to_date('"+endDate+"', 'yyyy-mm-dd'))");
                sbf.append(" and t.username in (select user_name from sddcgisplatform.user_info a,sddcgisplatform.user_organization b");
                sbf.append(" where a.user_id = b.user_id and b.organization_company_id  in (");
                sbf.append(" select id from sddcgisplatform.organization "+orgWhereSql+" ))");
			String result =  clusterDao.queryNormalResultList(sbf.toString());
			response.setResult(result);
			response.setSuccess(true);
		 }
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
		
		return response;
	}
	
	/**
	 * 导出聚合后的数据Excel
	 * @param clusterParam
	 * @param request
	 * @return
	 */
	public Response exportClusterLandData(String clusterParam, HttpServletRequest request) {
		Map map  = clusterDao.queryLandResult(clusterSql(clusterParam,request));
		List  resultList = markData(map);
		
		
		return null;
	}
	
	/**
	 * 导出Excel 
	 * @param departmentType
	 * @param projectType
	 * @param startdate
	 * @param enddate
	 * @param response
	 * @throws Exception
	 */
	public void exportlandSpecialdataExcel(String clusterParam, HttpServletRequest request, HttpServletResponse response) throws Exception {
		String filetitle = null;
		String[] rowsName = null;
		String[] columnTop = null;
		List<String> rowsList = new ArrayList<String>();
		List<String> columList = new ArrayList<String>();
		Response responseStr = clusterData(clusterParam, request);
 
		String[] landArr = clusterParam.split(",");
		int i=0;
		int j=0;
		
		rowsList.add(0, "");
		columList.add(0,"");
		
		for(String s : landArr){
			if("2".equals(s)){   //国有旧厂用地
				rowsList.add(i++,"国有旧厂用地");
				columList.add(++j,"");
			}else if("7".equals(s)){ //纳入三旧的旧村
				rowsList.add(i++,"纳入三旧的旧村");
				columList.add(++j,"");
			}else if("9".equals(s)){ //纳入三旧集体旧厂
				rowsList.add(i++,"纳入三旧集体旧厂");
				columList.add(++j,"");
			}else if("10".equals(s)){ //村自留地   留用地
				rowsList.add(i++,"村自留地");
				columList.add(++j,"");
			}
		}
		
		//TODO 判断为空
		
			filetitle = "聚合数据统计报表";
			rowsName  = rowsList.toArray(new String[rowsList.size()]);;
			columnTop = columList.toArray(new String[columList.size()]);;
	        
			String resultStr = (String) responseStr.getResult();
			JSONArray resultJsonArr = new JSONArray(resultStr);
			List<Map> Result = new ArrayList<Map>();
			
			if (resultJsonArr != null && resultJsonArr.length() > 0) {
	
				for (int t = 0; t< resultJsonArr.length(); t++) {
					JSONObject ret = (JSONObject) resultJsonArr.get(t);
					Iterator iterator = ret.keys();
					Map map = new HashMap();
					while (iterator.hasNext()) {
						String key = (String) iterator.next();
						String value = (String) ret.get(key);
						map.put(key, value);
					}
					Result.add(map);
				}
			}
			
			ExcelOperation excle = new ExcelOperation();
			excle.export(filetitle, rowsName, columnTop, Result, response);
	}
	 
	 
}
